The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
A SQL pivot operator pivots (or rotates) rows of a table into columns, performs aggregation on the measures specified, and stores the results in a result set. Pivot operations allow a user to specify multiple pivot columns in a table and multiple measures based on multiple columns of the table. Pivot operations are very useful in making and reporting comparative calculations.
Under some approaches, a user writes a pivoting query using existing SQL constructs such as group-by and aggregates. However, as the pivoting query uses existing SQL constructs as constituents, the resulting query is often too complex, even assuming the pivoting query operates correctly.
Under some other existing approaches, a user may use a new pivot operator (e.g., one or more keywords that are recognized by a database system) to cleanly and intuitively express a pivoting query. Internally, the database system still rewrites the pivoting query in terms of group-by and aggregation operations. A disadvantage of these approaches is that the rewritten (equivalent) query typically causes unnecessary data accesses and computations to underlying data.
Therefore, a better approach, which would better support efficient evaluation of SQL pivot operations, is needed.